use rusqlite::{params, Error, Result, ToSql};

use crate::common;

use chrono::{DateTime, Datelike, Local, Timelike};
use uuid::Uuid;

#[derive(serde::Serialize)]
pub struct UrlsClass {
    pub c_id: String,
    pub c_name: String,
}

#[derive(serde::Serialize)]
pub struct Urls {
    pub address_id: String,
    pub address_title: String,
    pub address_url: String,
    pub c_id: String,
}
pub fn get_url_class() -> Result<Vec<UrlsClass>> {
    let bind = common::DB_CONNECT.lock().unwrap();
    let conn = bind.as_ref().unwrap();
    //let conn = db::connect()?;

    let stmt = conn.prepare("select * from tools_urls_class");
    let mut binding = stmt?;
    let class_iter = match binding.query_map([], |row| {
        Ok(UrlsClass {
            c_id: row.get("c_id")?,
            c_name: row.get("c_name")?,
        })
    }) {
        Ok(iter) => iter,
        Err(e) => {
            return Ok(Vec::new());
        }
    };

    let mut all_class = Vec::new();
    for c in class_iter {
        all_class.push(c?);
    }
    Ok(all_class)
}

pub fn get_url(c_id: &str) -> Result<Vec<Urls>> {
    //let conn = db::connect()?;
    let bind = common::DB_CONNECT.lock().unwrap();
    let conn = bind.as_ref().unwrap();

    let stmt = conn.prepare("select * from tools_urls where c_id=?1");
    let mut binding = stmt?;
    let url_iter = match binding.query_map(params![c_id], |row| {
        Ok(Urls {
            address_id: row.get("address_id")?,
            address_title: row.get("address_title")?,
            address_url: row.get("address_url")?,
            c_id: row.get("c_id")?,
        })
    }) {
        Ok(iter) => iter,
        Err(e) => {
            return Ok(Vec::new());
        }
    };

    let mut urls = Vec::new();
    for u in url_iter {
        urls.push(u?);
    }

    Ok(urls)
}
pub fn del_url_by_id(id: &str) -> Result<()> {
    //let conn = db::connect()?;
    let bind = common::DB_CONNECT.lock().unwrap();
    let conn = bind.as_ref().unwrap();

    let sql = "delete from tools_urls where address_id=?1";
    conn.execute(sql, params![id])?;

    Ok(())
}
pub fn del_class_by_id(id: &str) -> Result<()> {
    //let conn = db::connect()?;
    let bind = common::DB_CONNECT.lock().unwrap();
    let conn = bind.as_ref().unwrap();

    let sql = "delete from tools_urls_class where c_id=?1";
    conn.execute(sql, params![id])?;

    let sql = "delete from tools_urls where c_id=?1";
    conn.execute(sql, params![id])?;

    Ok(())
}
pub fn save_class(c_id: &str, c_name: &str) -> Result<()> {
    //let conn = db::connect()?;
    let bind = common::DB_CONNECT.lock().unwrap();
    let conn = bind.as_ref().unwrap();

    if c_id.is_empty() {
        //新增
        let uuid = Uuid::new_v4();
        let local_time: DateTime<Local> = Local::now();
        let format_time = local_time.format("%Y-%m-%d %H:%M:%S").to_string();

        let sql = r#"insert into tools_urls_class (c_id,c_name,create_date,update_date,c_state) 
        values (?1,?2,?3,?4,?5)"#;
        conn.execute(
            sql,
            params![uuid.to_string(), c_name, &format_time, &format_time, "1"],
        )?;
    } else {
        //修改
        let sql = "update tools_urls_class set c_name=?1 where c_id=?2";
        conn.execute(sql, params![c_name, c_id])?;
    }

    Ok(())
}
pub fn save_url_form(
    address_id: &str,
    address_title: &str,
    address_url: &str,
    c_id: &str,
) -> Result<()> {
    //let conn = db::connect()?;
    let bind = common::DB_CONNECT.lock().unwrap();
    let conn = bind.as_ref().unwrap();

    if address_id.is_empty() {
        //新增
        let uuid = Uuid::new_v4();
        let local_time: DateTime<Local> = Local::now();
        let format_time = local_time.format("%Y-%m-%d %H:%M:%S").to_string();

        let sql = r#"insert into tools_urls (address_id,address_title,address_url,c_id,create_date,update_date) 
        values (?1,?2,?3,?4,?5,?6)"#;
        match conn.execute(
            sql,
            params![
                uuid.to_string(),
                address_title,
                address_url,
                c_id,
                &format_time,
                &format_time
            ],
        ) {
            Ok(_) => Ok(()),
            Err(e) => {
                println!("{:?}", e);
                return Err(e);
            }
        }
    } else {
        //修改
        let local_time: DateTime<Local> = Local::now();
        let format_time = local_time.format("%Y-%m-%d %H:%M:%S").to_string();

        let sql = "update tools_urls set address_title=?1,address_url=?2,c_id=?3,update_date=?4 where address_id=?5";
        match conn.execute(
            sql,
            params![address_title, address_url, c_id, format_time, address_id],
        ) {
            Ok(_) => Ok(()),
            Err(e) => {
                println!("{:?}", e);
                return Err(e);
            }
        }
    }

    //Ok(())
}
pub fn get_all_url(c_id: &str, key_word: &str) -> Result<Vec<Urls>> {
    //let conn = db::connect()?;
    let bind = common::DB_CONNECT.lock().unwrap();
    let conn = bind.as_ref().unwrap();

    if c_id == "all" && key_word.is_empty() {
        let stmt = conn.prepare("select * from tools_urls order by create_date desc");
        let binding = stmt?;

        build_url_list(binding, params![])
    } else if c_id == "all" && !key_word.is_empty() {
        let stmt = conn.prepare("select * from tools_urls where address_title like ?1 or address_url like ?2 order by create_date desc");
        let binding = stmt?;

        build_url_list(
            binding,
            params![format!("%{}%", key_word), format!("%{}%", key_word)],
        )
    } else if c_id != "all" && !key_word.is_empty() {
        let stmt = conn.prepare("select * from tools_urls where c_id=?1 and address_title like ?2 or address_url like ?3 order by create_date desc");
        let binding = stmt?;

        build_url_list(
            binding,
            params![c_id, format!("%{}%", key_word), format!("%{}%", key_word)],
        )
    } else {
        let stmt = conn.prepare("select * from tools_urls where c_id=?1 order by create_date desc");
        let binding = stmt?;

        build_url_list(binding, params![c_id])
    }
}
fn build_url_list(
    mut binding: rusqlite::Statement<'_>,
    params: &[&dyn ToSql],
) -> Result<Vec<Urls>, Error> {
    let url_iter = match binding.query_map(params, |row| {
        Ok(Urls {
            address_id: row.get("address_id")?,
            address_title: row.get("address_title")?,
            address_url: row.get("address_url")?,
            c_id: row.get("c_id")?,
        })
    }) {
        Ok(iter) => iter,
        Err(_) => return Ok(vec![]),
    };

    let mut urls = Vec::new();
    for u in url_iter {
        urls.push(u?);
    }

    Ok(urls)
}
